﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using NHibernateSample.Domain.Entities;
using NHibernate;
using NHibernate.Criterion;

namespace NHibernateSample.DAL
{
    public class QueryCriteriaAPI
    {
        protected ISession Session{get; set;}

        public QueryCriteriaAPI(ISession _session)
        {
            Session = _session;
        }

        #region 语法学习
        /// <summary>
        /// 创建ICriteria实例: 返回Customer对象集合，设置最大的集合数量为50条
        /// </summary>
        /// <returns></returns>
        public IList<Customer> CreateCriteria()
        {
            //NHibernate.ICriteria这个接口代表对一个特定的持久化类的查询。
            //ISession是用来制造Criteria实例的工厂。
            ICriteria crit = Session.CreateCriteria(typeof(Customer));
            crit.SetMaxResults(50);
            IList<Customer> customers = crit.List<Customer>();
            return customers;
        }

        /// <summary>
        /// 结果集限制:  缩小结果集范围
        /// </summary>
        /// <returns></returns>
        public IList<Customer> Narrowing()
        {
            //一个查询条件(Criterion)是NHibernate.ICriterion接口的一个实例。
            //类NHibernate.Expression.Expression定义了获得一些内置的ICriterion类型的工厂方法。

            IList<Customer> customers = Session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.Like("FirstName", "李"))
                .Add(Restrictions.Between("LastName", "A%", "Y%"))
                .List<Customer>();

            //表达式（Expressions）可以按照逻辑分组
            //IList<Customer> customers = Session.CreateCriteria(typeof(Customer))
            //    .Add(Restrictions.Like("FirstName", "YJ%"))
            //    .Add(Restrictions.Or(
            //    Restrictions.Eq("LastName", "L%"),
            //    Restrictions.IsNull("LastName")
            //    ))
            //    .List<Customer>();

            //IList<Customer> customers = Session.CreateCriteria(typeof(Customer))
            //    .Add(Restrictions.In("FirstName", new string[] { "YJing", "YJingLee", "Y" }))
            //    .Add(Restrictions.Disjunction()
            //    .Add(Restrictions.IsNull("LastName"))
            //    .Add(Restrictions.Eq("LastName", "Lee"))
            //    .Add(Restrictions.Eq("LastName", "xyz"))
            //    ).List<Customer>();

            //预制的条件类型（Expression的子类）。可以直接嵌入SQL。
            //{alias}是一个占位符，它将会被所查询实体的行别名所替代
            //Parameter paramName = new Parameter("someName", new StringSqlType());
            //IList<Customer> customers = Session.CreateCriteria(typeof(Customer))
            //    .Add(Expression.Sql(
            //      new SqlString(new object[]{
            //          "lower({alias}.LastName) like lower(","LastName",")"}), "YJing%", NHibernateUtil.String))
            //          .List<Customer>();
            return customers;
        }

        /// <summary>
        /// 结果集排序: 查询Customer对象按FirstName降序、LastName升序
        /// </summary>
        /// <returns></returns>
        public IList<Customer> Order()
        {
            //使用ICriteria.Order对结果集排序,true=asc,false=desc
            return Session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.Like("FirstName", "李"))
                .AddOrder(new NHibernate.Criterion.Order("FirstName", false))
                .AddOrder(new NHibernate.Criterion.Order("LastName", true))
                .List<Customer>();
        }

        /// <summary>
        /// 根据示例查询(Query By Example): 按照指定Customer查询数据库里的记录
        /// </summary>
        /// <returns></returns>
        public IList<Customer> Query()
        {
            //创建一个Example实例；在Example实例上设置值；根据Example和设置NHibernate返回其对象集合。
            Customer customerSample = new Customer() { FirstName = "董", LastName = "董" };
            return Session.CreateCriteria(typeof(Customer))
                .Add(Example.Create(customerSample))
                .List<Customer>();
        }

        /// <summary>
        /// 根据示例查询(QBE): 自行调整Example使之更实用
        /// </summary>
        /// <param name="customerSample"></param>
        /// <returns></returns>
        public IList<Customer> UseQueryByExample_GetCustomer(Customer customerSample)
        {
            Example example = Example.Create(customerSample)
                .IgnoreCase()
                .EnableLike()
                .SetEscapeCharacter('&');

            return Session.CreateCriteria(typeof(Customer))
                .Add(example)
                .List<Customer>();
        }
        #endregion

        #region 实例学习
        /// <summary>
        /// 利用CriteriaAPI按FirstName查询顾客
        /// </summary>
        /// <param name="firstname"></param>
        /// <returns>顾客列表</returns>
        public IList<Customer> UseCriteriaAPI_GetCustomersByFirstName(string firstname)
        {
            //NHibernate1.2写法
            //return Session.CreateCriteria(typeof(Customer))
            //    .Add(new NHibernate.Expression.EqExpression("FirstName", firstname))
            //    .List<Customer>();

            //NHibernate2.0写法
            //return Session.CreateCriteria(typeof(Customer))
            //    .Add(Expression.Eq("FirstName", firstname))
            //    .List<Customer>();
            //使用Name封装：.Add(Restrictions.Eq("Name.FirstName", firstname))
            return Session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.Eq("FirstName", firstname))
                .List<Customer>();
        }
        /// <summary>
        /// 利用CriteriaAPI按FirstName和LastName查询顾客
        /// </summary>
        /// <param name="firstname"></param>
        /// <param name="lastname"></param>
        /// <returns></returns>
        public IList<Customer> UseCriteriaAPI_GetCustomersByFirstNameAndLastName(string firstname, string lastname)
        {
            //NHibernate1.2写法
            //return Session.CreateCriteria(typeof(Customer))
            //    .Add(new NHibernate.Expression.EqExpression("FirstName", firstname))
            //    .Add(new NHibernate.Expression.EqExpression("LastName", lastname))
            //    .List<Customer>();

            //NHibernate2.0写法
            //使用Name封装：.Add(Restrictions.Eq("FirstName", firstname))
            return Session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.Eq("FirstName", firstname))
                .Add(Restrictions.Eq("LastName", lastname))
                .List<Customer>();
        }
        /// <summary>
        /// 利用CriteriaAPI获取顾客ID大于CustomerId的顾客
        /// </summary>
        /// <param name="customerId">顾客ID</param>
        /// <returns>顾客列表</returns>
        public IList<Customer> UseCriteriaAPI_GetCutomersWithIdGreaterThan(int customerId)
        {
            //NHibernate1.2写法
            //return Session.CreateCriteria(typeof(Customer))
            //    .Add(new NHibernate.Expression.GtExpression("Id", customerId))
            //    .List<Customer>();

            //NHibernate2.0写法
            return Session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.Gt("Id", customerId))
                .List<Customer>();
        }
        #endregion
    }
}
